<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="com.uc.training.smadmin.bd.dao.MemberGradeDao">

	<!-- 根据对象的不为null的值作为条件进行查找 -->
	<select id="getMemberGradeById" resultClass="com.uc.training.smadmin.bd.model.MemberGrade" parameterClass="long">
		 SELECT
			 s.id AS id,
			 s.name AS name,
			 s.min_growth AS minGrowth,
			 s.max_growth AS maxGrowth,
			 s.discount AS discount,
			 s.create_time AS createTime,
			 s.modify_time AS modifyTime,
			 s.create_emp AS createEmp,
			 s.modify_emp AS modifyEmp
		 FROM t_bd_member_grade s
		 WHERE s.id=#id#
	</select>


	<sql id="where_query">

		 <isNotEmpty property="id" prepend="and">
			 s.id = #id#
		 </isNotEmpty>
		 <isNotEmpty property="name" prepend="and">
			 s.name = #name#
		 </isNotEmpty>
		 <isNotEmpty property="minGrowth" prepend="and">
			 s.min_growth = #minGrowth#
		 </isNotEmpty>
		 <isNotEmpty property="maxGrowth" prepend="and">
			 s.max_growth = #maxGrowth#
		 </isNotEmpty>
		 <isNotEmpty property="discount" prepend="and">
			 s.discount = #discount#
		 </isNotEmpty>
		 <isNotEmpty property="createTime" prepend="and">
			 s.create_time = #createTime#
		 </isNotEmpty>
		 <isNotEmpty property="modifyTime" prepend="and">
			 s.modify_time = #modifyTime#
		 </isNotEmpty>
		 <isNotEmpty property="createEmp" prepend="and">
			 s.create_emp = #createEmp#
		 </isNotEmpty>
		 <isNotEmpty property="modifyEmp" prepend="and">
			 s.modify_emp = #modifyEmp#
		 </isNotEmpty>
	</sql>

	<!-- 列表查询 -->
	<select id="queryMemberGradeList" resultClass="com.uc.training.smadmin.bd.model.MemberGrade">
		 SELECT
		 	 s.id AS id,
			 s.name AS name,
			 s.min_growth AS minGrowth,
			 s.max_growth AS maxGrowth,
			 s.discount AS discount
		 FROM t_bd_member_grade s
	</select>

	<!-- 查找数据总记录数 -->
	<select id="queryMemberGradeCount" resultClass="java.lang.Integer">
		 SELECT count(1)
		 FROM t_bd_member_grade s
		 WHERE 1=1
	</select>

	<!-- 插入 -->
	<insert id="insertMemberGrade" parameterClass="com.uc.training.smadmin.bd.model.MemberGrade">
		 insert into t_bd_member_grade
			 (id, name, min_growth, max_growth, discount, create_time, modify_time, create_emp, modify_emp)		 values (#id#,#name#,#minGrowth#,#maxGrowth#,#discount#,#createTime#,#modifyTime#,#createEmp#,#modifyEmp#)
	<selectKey keyProperty="id">
		select LAST_INSERT_ID() AS id
	</selectKey>
	</insert>

	<!-- 通过主键更新 -->
	<update id="updateMemberGradeById" parameterClass="com.uc.training.smadmin.bd.model.MemberGrade">
		update t_bd_member_grade set
			discount = #discount#,
			modify_emp = #modifyEmp#
		where id=#id#
	</update>

	<!-- 通过用户ID获取会员等级信息 -->
	<select id="getByUId" parameterClass="Long" resultClass="com.uc.training.smadmin.bd.model.MemberGrade">
		<![CDATA[
		SELECT
		  g.id,
		  g.name,
		  g.min_growth,
		  g.max_growth,
		  g.discount
		FROM
		  t_bd_member_grade g,
		  t_bd_member m
		WHERE
		  m.id = #id# AND
		  m.growth >= g.min_growth AND
		  m.growth < g.max_growth
		]]>
	</select>
</sqlMap>